Imports Microsoft.VisualBasic
Imports System.Data

Public Class UsuarioDAL

    Public Function Login(ByRef Usuario As Usuario) As Boolean

        Dim Result As Boolean
        Dim QuerySQL As String
        Dim oSqlConnection As SqlClient.SqlConnection
        Dim oSqlCommand As SqlClient.SqlCommand
        Dim oNomeIdentificacaoParameter As SqlClient.SqlParameter
        Dim oSenhaParameter As SqlClient.SqlParameter
        Dim UsuarioID As Integer

        Try

            oSqlConnection = New SqlClient.SqlConnection
            oSqlCommand = New SqlClient.SqlCommand

            oSqlConnection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("DefaultConnectionString").ConnectionString

            oNomeIdentificacaoParameter = New SqlClient.SqlParameter("NomeIdentificacao", SqlDbType.VarChar, 100)
            oNomeIdentificacaoParameter.Value = Usuario.Login

            oSenhaParameter = New SqlClient.SqlParameter("Senha", SqlDbType.VarChar, 8)
            oSenhaParameter.Value = Usuario.Senha

            QuerySQL = "SELECT UsuarioID FROM Usuario WHERE NomeIdentificacao = @NomeIdentificacao AND Senha = @Senha"

            With oSqlCommand
                .CommandText = QuerySQL
                .CommandType = CommandType.Text
                .Connection = oSqlConnection
                .Parameters.Add(oNomeIdentificacaoParameter)
                .Parameters.Add(oSenhaParameter)
                .Connection.Open()
            End With

            UsuarioID = Convert.ToInt32(oSqlCommand.ExecuteScalar)

            Result = UsuarioID > 0

            Usuario.UsuarioID = UsuarioID

            If Result Then
                SetUserProperties(Usuario)
            Else
                Usuario = Nothing
            End If

            Return Result

        Catch ex As Exception

            Return False

        End Try

    End Function

    Private Sub SetUserProperties(ByRef Usuario As Usuario)

        Dim QuerySQL As String
        Dim oSqlConnection As SqlClient.SqlConnection
        Dim oSqlCommand As SqlClient.SqlCommand
        Dim oUsuarioIDParameter As SqlClient.SqlParameter
        Dim oDataReader As SqlClient.SqlDataReader

        Try

            oSqlConnection = New SqlClient.SqlConnection
            oSqlCommand = New SqlClient.SqlCommand

            oSqlConnection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("DefaultConnectionString").ConnectionString

            oUsuarioIDParameter = New SqlClient.SqlParameter("UsuarioID", SqlDbType.Int, 4)
            oUsuarioIDParameter.Value = Usuario.UsuarioID

            QuerySQL = "SELECT NomeIdentificacao, Senha, Nome, Administrador FROM Usuario WHERE UsuarioID = @UsuarioID"

            With oSqlCommand
                .CommandText = QuerySQL
                .CommandType = CommandType.Text
                .Connection = oSqlConnection
                .Parameters.Add(oUsuarioIDParameter)
                .Connection.Open()
            End With

            oDataReader = oSqlCommand.ExecuteReader()

            While oDataReader.Read

                With Usuario
                    .Login = Convert.ToString(oDataReader("NomeIdentificacao"))
                    .Senha = Convert.ToString(oDataReader("Senha"))
                    .Nome = Convert.ToString(oDataReader("Nome"))
                    .Administrador = Convert.ToDouble(oDataReader("Administrador"))
                End With

            End While

        Catch ex As Exception

            Usuario = Nothing

        End Try

    End Sub

End Class